﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.PPMS;

namespace yunbo.Repositorys.PPMSRepositorys
{
    public class Mes_SCGDRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public List<Mes_SCGD> GetMes_SCGD()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "select* from mes_scgd";
                List<Mes_SCGD> mes_SCGD = connection.Query<Mes_SCGD>(sql).ToList();
                return mes_SCGD;
            }
        }

        public List<Mes_SCGD> GetMes_scjhcode(string scjh_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select* from mes_scgd where scjh_code='{scjh_code}'";
                List<Mes_SCGD> mes_SCGD = connection.Query<Mes_SCGD>(sql).ToList();
                return mes_SCGD;
            }
        }

        public int AddMes_SCGD(ADDSCGD scgd)
        {
            // 新的 SQL 插入语句
            string sql = @"INSERT INTO [dbo].[mes_scgd] (gd_state, gd_yxj, gd_code, gd_name, cp_code, cp_name, cp_xh, cp_lx,cp_fl, dw_code, dw_name,pp_code, pp_name,  ks_code,ks_name, bom_code, gd_jhsl, gd_sjscsl, gd_xqrq, gd_jhkgsj, gd_jhwgsj, gd_bz,scjh_code, create_by, create_time,ht_code)
                   VALUES (59, @gd_yxj, @gd_code, @gd_name, @cp_code, @cp_name, @cp_xh, @cp_lx,@cp_fl, @dw_code, @dw_name,@pp_code, @pp_name, @ks_code,@ks_name,@bom_code, @gd_jhsl, @gd_sjscsl, @gd_xqrq,@gd_jhkgsj,@gd_jhwgsj,@gd_bz,@scjh_code, @create_by, GETDATE(),@ht_code)";
            try
            {
                using (SqlConnection connection = new SqlConnection(data))
                {
                    connection.Open();
                    // 调整参数列表以匹配新的 SQL 语句
                    var parameters = new
                    {
                        gd_yxj = scgd.gd_yxj, 
                        gd_code = scgd.gd_code, 
                        gd_name = scgd.gd_name, 
                        cp_code = scgd.cp_code, 
                        cp_name = scgd.cp_name, 
                        cp_xh = scgd.cp_xh, 
                        cp_lx = scgd.cp_lx,
                        cp_fl = scgd.cp_fl,
                        dw_code = scgd.dw_code, 
                        dw_name = scgd.dw_name, 
                        ks_code = scgd.ks_code,
                        ks_name= scgd.ks_name,
                        pp_code = scgd.pp_code,
                        pp_name = scgd.pp_name,
                        bom_code = scgd.bom_code, 
                        gd_jhsl = scgd.gd_jhsl, 
                        gd_sjscsl = scgd.gd_sjscsl, 
                        gd_xqrq = scgd.gd_xqrq, 
                        gd_jhkgsj = scgd.gd_jhkgsj, 
                        gd_jhwgsj = scgd.gd_jhwgsj, 
                        gd_bz = scgd.gd_bz, 
                        scjh_code = scgd.scjh_code, 
                        create_by = scgd.create_by,
                        ht_code=scgd.ht_code,
                    };

                    var affectedRows = connection.Execute(sql, parameters);
                    return affectedRows;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
        }

        public List<int> GetStatus(string scjh_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select gd_state from mes_scgd where scjh_code='{scjh_code}'";
                List<int> status = connection.Query<int>(sql).ToList();
                return status;
            }
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="code"></param>
        /// <param name="state"></param>
        /// <param name="update_by"></param>
        /// <returns></returns>
        public int UpdateStatus(string scgd_code, int state, int update_by, string scjh_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "";
                if (scgd_code == "1")
                {
                    sql = "UPDATE mes_scgd SET gd_state = @state, update_by = @update_by, update_time = GETDATE() WHERE scjh_code = @scjh_code";
                }
                else
                {
                    sql = "UPDATE mes_scgd SET gd_state = @state, update_by = @update_by, update_time = GETDATE() WHERE gd_code = @scgd_code";
                }
                // 使用参数化查询来防止 SQL 注入

                // 创建命令对象并设置参数
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@scjh_code", scjh_code);
                    command.Parameters.AddWithValue("@scgd_code", scgd_code);
                    command.Parameters.AddWithValue("@update_by", update_by);
                    command.Parameters.AddWithValue("@state", state); // 注意：如果 cp_code 是数值类型，则不应该加引号

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }

        /// <summary>
        /// 删除生产工单
        /// </summary>
        /// <param name="scdd_code"></param>
        /// <returns></returns>
        public int DeleteScgd(string scjh_code)
        {
            int rowsAffected = 0;
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 修正 SQL 语句
                string sql = $"DELETE FROM mes_scgd WHERE scjh_code = @ScjhCode";
                // 使用 Execute 方法执行删除操作
                return rowsAffected = connection.Execute(sql, new { ScjhCode = scjh_code });
            }
        }
        public int UpdateScgdJD(int zt,int sl,int jd,string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 修正 SQL 语句
                string sql = $"update [dbo].[mes_scgd] set gd_state={zt},gd_sjscsl={sl},gd_jd={jd} where gd_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;

            }
        }
        public Mes_SCGD GetGD(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                // 修正 SQL 语句
                string sql = $"select * from [dbo].[mes_scgd] where gd_code='{code}'";
                Mes_SCGD rs = connection.Query<Mes_SCGD>(sql).First();
                return rs;

            }
        }

    }
}
